﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spAgent_Job_StartWait]
#-- Purpose:		Provides a mechanism to call a SQLAgent Job from another
#--					job controller system, wait for completion, and return
#--					the results to standard out.
#--	Last Update:	01/22/2014
#--					For a complete history - please review comments in SVN
#--
#--			--- http://www.mssqltips.com/tip.asp?tip=2167
#--			-----------------------------------------------
#--			--- Requires the "Log to Table" on Job Step to 
#--			---		be turned on.
#--			-----------------------------------------------
#--
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spAgent_Job_StartWait]
(
	@job_name SYSNAME,
	@WaitTime DATETIME = '00:00:05', -- this is parameter for check frequency
	@JobCompletionStatus INT = NULL OUTPUT
)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_owner SYSNAME
DECLARE @start_date DATETIME
DECLARE @job_Message NVARCHAR(MAX)
DECLARE @error VARCHAR(MAX)

--Createing TEMP TABLE
CREATE TABLE #xp_results (
	job_id UNIQUEIDENTIFIER NOT NULL,
	last_run_date INT NOT NULL,
	last_run_time INT NOT NULL,
	next_run_date INT NOT NULL,
	next_run_time INT NOT NULL,
	next_run_schedule_id INT NOT NULL,
	requested_to_run INT NOT NULL, -- BOOL
	request_source INT NOT NULL,
	request_source_id SYSNAME COLLATE database_default NULL,
	running INT NOT NULL,
	current_step INT NOT NULL,
	current_retry_attempt INT NOT NULL,
	job_state INT NOT NULL
	)

SELECT @job_id = [job_id]
FROM [msdb].[dbo].[sysjobs]
WHERE [name] = @job_name

SELECT @job_owner = SUSER_SNAME()

--- Initialize Local Variables		
SELECT @start_date = GETDATE()

--- Print Start Time
PRINT 'Job [' + @job_name + '] started at ' + CONVERT(VARCHAR(20), @start_date, 120)
PRINT ''

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,
	@job_owner,
	@job_id

-- Start the job if the job is not running
IF NOT EXISTS (
		SELECT TOP 1 *
		FROM #xp_results
		WHERE running = 1
		)
	EXEC [msdb].[dbo].[sp_start_job] @job_name = @job_name

-- Give 2 sec for think time.
WAITFOR DELAY '00:00:02'

DELETE
FROM #xp_results

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,
	@job_owner,
	@job_id

WHILE EXISTS (
		SELECT TOP 1 *
		FROM #xp_results
		WHERE running = 1
		)
BEGIN
	WAITFOR DELAY @WaitTime

	-- Information 
	RAISERROR (
			'JOB IS RUNNING',
			0,
			1
			)
	WITH NOWAIT

	DELETE
	FROM #xp_results

	INSERT INTO #xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,
		@job_owner,
		@job_id
END

SELECT TOP 1 @JobCompletionStatus = [run_status],
	@job_Message = [message]
FROM [msdb].[dbo].[sysjobhistory]
WHERE [job_id] = @job_id
	AND [step_id] = 0
ORDER BY [run_date] DESC,
	[run_time] DESC

--- Print Start Time
PRINT ''

IF @JobCompletionStatus = 1
	PRINT 'Job [' + @job_name + '] finished successfully at ' + CONVERT(VARCHAR(20), GETDATE(), 120)
		--PRINT 'The job ran Successful' 
ELSE
	IF @JobCompletionStatus = 3
		PRINT 'The job is Cancelled'
	ELSE
	BEGIN
		SET @error = '[ERROR]:%s job is either failed or not in good state. Error Message: ' + @job_Message + ' Call POC.'

		RAISERROR (
				@error,
				16,
				1,
				@job_name
				)
		WITH LOG
	END

RETURN @JobCompletionStatus
